Previous Page TOC Next Page


29 — Open Database Connectivity (ODBC)

by Brad Shannon

Open Database Connectivity (ODBC) is a standard developed by Microsoft to simplify the development of applications that may have to target a variety of Database platforms. ODBC is implemented through a collection of database drivers that are provided by Microsoft and third-party database vendors. ODBC database drivers provide your application with the capability to utilize a variety of database formats through the use of driver libraries. When using ODBC databases, it is possible to support more than one database format using the same application source code. In order to switch databases, you would only need to switch ODBC drivers.

Examining the Levels of ODBC Compliance

All ODBC-type databases are SQL-based. The developers of the ODBC drivers decide to which level of the ODBC specification the driver will be compliant. For a driver to be compliant, it must support all aspects of that level. There are three levels of ODBC driver compliance:

  1. Core. This level contains all the tools needed to perform the following tasks:

    Opening and closing a database
    Supporting ODBC core data types
    Executing supported core-level SQL statements
    Retrieving and updating data
    Supporting the use of database level transactions
    Providing error information when an error condition exists

  2. Level 1. This consists of all the core functionality plus additional tools that can be used on the database. The additional functionality consists of the following:

    Executing supported level 1 SQL statements
    Supporting ODBC level 1 data types
    Providing information about connections to the database
    Retrieving or writing partial data to a field
    Retrieving statistical and structural information about the database and its objects
    Retrieving information about the data types supported by the database
    Retrieving database driver information—the version of the database driver, for example

  3. Level 2. This consists of all of the core functionality, level 1 functionality, and the additional tools that can be used on the database. The additional functionality consists of the following:

  4. Providing information about connections to the database and all the other available data sources (such as other ODBC databases) installed on the users PC

    Executing supported level 2 SQL statements
    Supporting ODBC level 2 data types
    Providing a description for parameters in an SQL statement
    Providing a scrollable cursor
    Retrieving information from the database catalog
    Retrieving and executing stored procedures contained in the database
    Retrieving and setting index information contained in the database
    Using a translation DLL when working in a multilanguage database

The ODBC driver developer generally provides information regarding the level of compatibility of the drivers, as well as functions, methods, and data types that are supported. It also is possible for your application to determine the level of conformance by using the following ODBC API calls (all ODBC API calls are located in the ODBC.DLL library included with Visual Basic):

Using ODBC, it is possible to have one application that can host a variety of database platforms using the same application code. This is accomplished through the use of ODBC database drivers that support the target database. With ODBC, it is possible to create an application based on one ODBC-based database engine. After that application has been completed, you then can substitute an alternative ODBC-based database engine and your application will perform all its operations against the new ODBC-based database engine.

Looking at the Benefits of ODBC

The primary benefit of ODBC is that it enables your application to be database independent. To achieve the maximum benefit from ODBC, you must decide early in the design stage whether this is the route you want to follow. You must follow certain rules to fully exploit the ODBC layer.

ODBC allows the developer to create an application using a locally available ODBC-compliant database (Oracle, for example). After the application is completed, the process to move the application to another ODBC database (an SQL server, for example) is as simple as changing the ODBC connect string in the OpenDataBase function.

Listings 29.1 through 29.3 provide examples in order to illustrate the different methods available for opening an ODBC-type database. The methods illustrated here utilize the different parameters that can be used in conjunction with the OpenDatabase method of a Workspace object for ODBC databases. 

Dim db As DataBase

Dim ws As WorkSpace

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

Set db = ws.OpenDatabase("", False, False, "ODBC;")

This example prompts the user to select from a list of previously defined ODBC data sources. The user will be prompted as the final step in opening the database. After the user selects a database, the database opens using the security information provided in the CreateWorkspace statement. If the user chooses the Cancel option, a trappable error is generated.


Figure 29.1. The ODBC selection dialog box. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase=BIBLIO;UID=ADMIN;PWD=ADMIN"

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

Listing 29.2 opens the BIBLIO database and associates it to the data source type ORACLE. The user ID, ADMIN, and an empty password field are displayed. The user will have to enter a valid password prior to the database being opened. If the database cannot be opened, a trappable error is generated.

NOTE

The connect string varies between different ODBC drivers. 
Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase=BIBLIO;UID=;PWD="

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

Listing 29.3 opens the BIBLIO database and associates it to the data source type ORACLE. Because the User ID and the password items have been omitted, a database Logon dialog box is displayed (see Figure 29.2). If the user chooses the Cancel option, a trappable error is generated. If the database cannot be opened, a trappable error is generated.


Figure 29.2. The database Logon dialog box.

Using ODBC with the Data Control

Utilizing the Data control or Data Access objects, you are able to manipulate an ODBC data source in the same manner as that of native Microsoft Access databases.

Before you can access an ODBC database, you first must set up the database using the ODBC application shipped as part of Visual Basic. You can find this application in the Windows Control Panel or in the Visual Basic program group.

To set up an ODBC source, perform the following steps (this example assumes that the ORACLE Version 7.1 ODBC driver has been installed on your system, but these steps would apply to all ODBC drivers):

  1. Double-click on the ODBC configuration option, which is contained in the Windows Control panel application.

  2. When the ODBC configuration application has started, you will be presented with a list of the ODBC data sources that are currently set up on your PC. Select the Add button to add a new ODBC data source.

  3. You will now be presented with a list of ODBC drivers that are currently installed on your system. Double-click on the ORACLE71 ODBC Driver.

  4. Once you have selected the ORACLE71 driver, you will then be presented with a dialog box similar to that displayed in Figure 29.3


    5. Figure 29.3. The ODBC Oracle Setup dialog box.

  5. In the Data Source field, you enter the name of the data source as it is used in your application. For this example, enter BIBLIO.

  6. In the Description field, type Chapter 29.

  7. In the SQL *Net Connect String field, type connect. This will provide the Oracle driver with the location of the Oracle server. The information contained in this field is generally the name of the File server on which the Oracle Database server is installed. For additional information on the connect string, see the Oracle SQL *Net documentation supplied with your Oracle server.

NOTE

The above SQL *Net setting is specific to Oracle and may be different for other ODBC drivers. Other database drivers may require additional or different information. The driver specific requirements will be provided with the ODBC documentation, provided by the vendor.
  1. Once you have completed the Setup, press the OK button to exit the ODBC data source Configuration utility. You will then be returned to the data source selection display. Your new data source will now be visible. Press the Close button in order to exit the ODBC configuration utility.

Examining Specific Properties Used with an ODBC Database

The properties, methods, and events of the Data control have been fully described in Chapter 27, "The Data Control." The following section will deal only with the properties, methods, and events that are specific to the use of the Data control with ODBC types of databases.

Connect

When connecting to an ODBC database, you must specify ODBC in the Connect property, and perhaps supply additional information such as user ID and password, as well as any additional information necessary dependent on the type of ODBC database you are using. Further information on the Connect string for ODBC databases is supplied in the ODBC database driver documentation.

To set the Connect property at design time, enter the Connect string using the Property Editor.

To set the Connect property at runtime, use the following expression: 

data1.connect= "OBDC;Database= BIBLIO;UID=ADMIN;PWD=ADMIN"
NOTE

To utilize an ODBC database in your application, it must have been previously registered. The steps you need to take to register a data source have been outlined in the section "Using ODBC with the Data Control."
DatabaseName

If you are using an ODBC type of database, this property must be left blank.

To set this property at design time, clear any entry contained in the DatabaseName property.

To set this property at runtime, use the following expression: 

data1.DatabaseName=""
Options

When using an ODBC database, you can set the Data control so that all SQL statements will be executed on the database server and not interpreted by the Jet Engine (the default Database engine supplied with Visual Basic). You would need to use the Options setting in the following situations:

To set the Options property at design time, enter the dbSQLPassThrough constant into the Options property using the Property Editor.

To set this property at runtime, use this expression: 

data1.options= dbSQLPassThrough
NOTE

If you use SQLPassThrough, you cannot use any of the Find methods on the recordset that has been created.

Looking at an Example of a Bound ODBC Database

This section provides an example of opening an ORACLE database that is a copy of the BIBLIO database. This example is based on the bound controls example discussed in Chapter 27.

NOTE

The example uses an Oracle database that is an exact copy of the BIBLIO database supplied with Visual Basic.
  1. Using the File | New Project command, open a new project.

  2. Add the controls listed below to your form either by double-clicking their representation in the toolbar or by selecting the control and drawing it on the form. Once all the controls have been added to the form, access their property setting and make the following changes: 
Control


Name


Caption


Top


Left


Width


Height

FORM

Form1

Bound Form

1515

1920

6810

5190

Data

Data1

Data control

3360

0

6495

300

Label

lblPubid

Pub ID.

180

180

1215

285

Label

lblYear

Year Pub.

180

3660

1215

285

Label

lblIsbn

ISBN

600

180

1215

285

Label

lblTitle

Title

1020

180

1215

285

Label

lblSubject

Subject

1440

180

1215

285

Label

lblDescription

Description

1860

180

1215

285

Label

lblComments

Comments

2280

180

1215

285

Label

lblNotes

Notes

2700

180

1215

285

TextBox

txtPubid


180

1560

855

285

TextBox

txtYear


180

5040

855

285

TextBox

txtIsbn


600

1560

1635

285

TextBox

txtTitle


1020

1560

4635

285

TextBox

txtSubject


1440

1560

4635

285

TextBox

txtDescription


1860

1560

4635

285

TextBox

txtComments


2280

1560

4635

285

TextBox

txtNotes


2700

1560

4635

285

Command

cmdExit

E&xit

4080

5400

795

295

  1. Set the following properties for the Data1 Data control:

    Connect= ODBC;DATABASE=BIBLIO;UID=Admin;PWD=Admin
    DatabaseName =
    RecordSource = Select * from titles

  2. Set the DataSource and DataField properties for the text boxes as indicated in Listing 29.4 for the text boxes that were added to the form in step two.

    You set the DataSource property by entering the name of the Data control—in this case, data1—or by selecting it from the combo box that appears in the DataSource property. After you set the DataSource property, you select the DataField property associated with the bound control by entering the name of the field in the underlying record set or selecting it from the combo box that appears in the DataField property. 

TextBox txtPubid

   DataSource = data1

   DataField =  Pubid

TextBox  txtYear

   DataSource = data1

   DataField =  Year Published

TextBox txtIsbn

   DataSource = data1

   DataField = Isbn

TextBox txtTitle

   DataSource = data1

   DataField = Title

TextBox txtSubject

   DataSource = data1

   DataField = Subject

TextBox txtDescription

   DataSource = data1

   DataField = Description

TextBox txtComments

   DataSource = data1

   DataField = Comments

TextBox txtNotes

   DataSource = data1

   DataField = Notes
  1. In the Click event for the cmdExit control, enter the following line:

    Unload Me

  2. Press the F5 key in order to run the project.

A window like the one shown in Figure 29.4 appears. Using the Data control's record navigation buttons, it is possible to move through all of the records contained in the title's recordset. Additionally, you can make changes to the contents of the recordset by altering the information contained in the text boxes and using the Data control to change the current record.


Figure 29.4. Output from the bound Oracle database.

As you can see, you were able to switch the database that this application was using to another database with only changes being made to the Connect, DatabaseName, and the RecordSource properties.

Using ODBC with Data Access Objects

You can use Data Access Objects (DAOs) to manipulate an ODBC database in the same manner as that of Microsoft Access databases. Primarily, this section will cover aspects of the DAO that are of particular importance when dealing with ODBC types of databases. For a detailed look at the other facilities available in the Data Access Object, please refer to Chapter 28, "The Access Jet Engine."

Specific Properties of the DAO Used with an ODBC Database

The following properties of the DAO are to be used in conjunction with ODBC types of databases. Many of the properties examined here are to be used only with ODBC types of databases, hence they were only briefly covered in Chapter 28.

CacheSize

The CacheSize property of the Recordset object defines the number of records in the record set that should be stored in a local cache. The CacheSize property is not available on table or Snapshot types of recordsets.

CacheStart

The CacheStart property of the Recordset object defines the first cacheable record in the record set. This property is not available on table or Snapshot types of recordsets.

Connect

When opening a QueryDef (stored procedure) that is contained in an ODBC database, you need to specify ODBC in the Connect string, and perhaps supply additional information such as user ID and password. The additional information that needs to be specified depends on the type of ODBC database. The additional information that must be supplied in the Connect string for the ODBC database is supplied in the ODBC database driver documentation.

ExecuteSQL

The ExecuteSQL method of the database object is used only with ODBC-type databases. This method is provided for compatibility with earlier versions of Visual Basic. The ExecuteSQL method returns the number of rows affected. The SQL contained in the ExecuteSQL method must be a nonrecord-returning SQL statement.

The Execute method has the SQLStatement parameter, which is a nonrecord-returning SQL statement.

Listing 29.5 illustrates the use of the ExecuteSQL method. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Dim rs As Recordset

Dim nrows As Long

Dim csql As String

' Now open the DataBase

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase= BIBLIO,UID=ADMIN,PWD=Admin"

fSet db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

Set rs = db.OpenRecordset("select * from titles", dbOpenDynaset, dbSQLPassThrough)

csql = " update titles set notes = 'Test Notes' "

nrows = db.ExecuteSQL(csql)

If this code were run against an ODBC-based database, the number of modified records would be contained in the nrows field.

FillCache

The FillCache property of the Recordset object fills the previously allocated cache of the recordset with records from the database. When using the FillCache property, you must specify the number of records to retrieve, as well as the record number at which the retrieval is to begin. The FillCache property is not available on table or Snapshot types of record sets.

Listing 29.6 illustrates the use of the CacheSize and CacheStart properties and the FillCache method of a recordset created from an ODBC data source. The FillCache method will fill the cache with 25 records, starting with record 25 in the rs recordset. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Dim rs As Recordset

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

' Now open the DataBase

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

Set rs = db.OpenRecordset("select * from titles", dbOpenDynaset, dbSQLPassThrough)

rs.CacheSize = 25

rs.CacheStart = rs.Bookmark

rs.FillCache rs.CacheSize, rs.CacheStart
LoginTimeout

The LoginTimeout property of the DBEngine object instructs the Jet Engine how long it should wait for a response to a Login request from an ODBC database. The LoginTimeout property defaults to 20 seconds. If you want your application to wait an indefinite period of time, set the value to 0.

LogMessages

You use the LogMessages property to indicate whether the messages received during execution of a QueryDef against an ODBC database should be logged. The LogMessages property must be added to the QueryDef object using the CreateProperty method. All the logged messages are stored in an ADMIN-xx table.

Listing 29.7 illustrates the use of the LogMessages property method. 

Dim ws As Workspace

Dim db As Database

Dim qrynew As QueryDef

Dim rstitles As Recordset

Dim propmessages As Property

Dim sconnect As String

Set ws = DBEngine.CreateWorkspace("title", "Admin", "")

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

' Create a New QueryDef

Set qrynew = db.CreateQueryDef("new", "Select * from titles where pubid = 13")

' Set the connect information for the Query

sconnect = "DataBase= BIBLIO,UID=ADMIN,PWD=Admin"

qrynew.Connect = sconnect

' Load The Recordset based on the New Query

Set rstitles = db.OpenRecordset("new", dbOpenDynaset)

Set propmessages = qrynew.CreateProperty("logmessages", dbBoolean, True, False)

' Save the Property definition

qrynew.Properties.Append propmessages

' Set the Query to Log messages

qrynew.logmessages = True
ODBCTimeout

The ODBCTimeout property of the QueryDef object indicates the length of time the Jet Engine waits for a reply from an ODBC database before returning a time-out error to your application. The default value for the QueryTimeOut property is 60 seconds. To force the QueryDef not to time-out, set the property to 0. The value of the ODBCTimeout overrides the value set for the QueryTimeOut property of the database object.

OpenDataBase

The OpenDataBase method or the WorkSpace object creates a connection between your application and the ODBC database and assigns it to a database type object. The OpenDataBase method contains the following parameters:

Listing 29.8 illustrates the opening of a registered Oracle database. If the database is not registered, a trappable error is generated. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase=BIBLIO;UID=ADMIN;PWD="

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)
Option

The Option parameter of the OpenRecordset, CreateDynaset, CreateSnapshot, OpenTable, and Execute methods of the DataBase, QueryDef, and Recordset objects must be set to dbSQLPassThrough if you want the SQL statements associated with the creation of the object to be processed on the database server and not be interpreted by the Jet Engine. This option is needed in the following situations:

Listing 29.9 shows how to use the dbSQLPassThrough option when creating a record set from an ODBC data source. This causes the SQL statement to be passed on to the database server for processing. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

Dim rs As Recordset

' Now open the DataBase

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase= BIBLIO,UID=ADMIN,PWD=Admin"

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

Set rs = db.OpenRecordset("select * from titles", dbOpenDynaset, dbSQLPassThrough)
QueryTimeout

You use the QueryTimeout property or the DataBase object to set the amount of time the DataBase object waits for a response to a query issued by the execute or ExecuteSQL method of the DataBase object. The value of the QueryTimeout property applies to all SQL (such as ExecuteSQL) requests made to the ODBC data source. The QueryTimeout property defaults to 60 seconds.

RegisterDataBase

The RegisterDataBase method of the DBEngine records information associated with the data source. This information includes the name of the data source, the type of data source, and the driver library that will be used to access the data source. This information is then stored in the ODBC.INI file. The driver for the ODBC source reads this information and uses it to open the connection to the data source. Because the data source needs to be registered only once, it is perhaps a better idea to use the ODBC function located in the Windows Control Panel to register your database, or to include the registration as part of your application setup.

The RegisterDataBase method has four parts to it:

  1. The name of the data source that will be used by the OpenDataBase method.

  2. The name of the ODBC driver.

  3. Whether you want the Register DataSource dialog box to appear. A setting of True causes the ODBC DataSource Registration dialog box to be displayed in order to prompt the user for additional information, or to verify that the data has been entered correctly.

  4. The attributes associated with the database driver. An example of the attributes would be the SQL *Net location that would have to be specified for an Oracle database.

Listing 29.10 illustrates the steps necessary to register the BIBLIO data source of the type of ORACLE. 

Dim db As Database

Dim ws As Workspace

Dim sconnect As String

sconnect = "network1"

DBEngine.RegisterDatabase "BIBLIO", "ORACLE", True, sconnect

' Now open the Database

Set ws = DBEngine.CreateWorkspace("ws1", "ADMIN", "")

sconnect = "DataBase= BIBLIO,UID=ADMIN,PWD=Admin"

Set db = ws.OpenDatabase("BIBLIO", False, False, "ODBC;" & sconnect)

DAO Methods Inappropriate for Use with ODBC Databases

The following methods of the Data Access Object DBEngine cannot be used with an ODBC type of database:

You are also unable to use the following database object creation methods, as each database handles the creation of its objects in a different manner:

As mentioned earlier, the supplier of your database software will include separate tools that will enable you to perform these functions.

Using ODBC with the ODBC API

Using the API effectively bypasses all Jet Engine interaction with the ODBC database. When using the ODBC API, you cannot use the native Visual Basic Data control or any of the bound control features of Visual Basic.

All the functions contained in the ODBC API are contained in the ODBC.DLL library.

Using the ODBC API is beyond the scope of this book, but I will touch briefly on some of the most important functions contained in the ODBC API. The majority of the calls outlined here are provided in the core level of compliance. You can find more detailed information in the Microsoft ODBC SDK Guide. Where possible, the Visual Basic equivalents are listed:

Putting the API Calls Together

When using the ODBC API, there are certain steps that must be performed each time that a session is to be established with the database server. The following functions are necessary to perform the steps:

  1. SQLAllocEnv. This API call will initiate the conversion between your application and the ODBC driver manager. Additionally, this function will allocate memory that will be used to store a handle to the driver manager. This function must be used prior to any other ODBC API function being used.

  2. SQLAllocConnect. Using the information that is retrieved by the SQLAllocEnv function, this function will allocate memory that will be used to store a handle to the database connection.

  3. SQLConnect. Using information that has been retrieved by the SQLAllocConnect function, this function will perform the connection as well as logon to the specified database.

Listing 29.11 illustrates the use of the preceding ODBC API functions in order to establish a session and open a database.

Note

All of the ODBC API functions return a value indicating whether the API function was successful, The following constants should be added to a public module contained in your project:

Public Const SQL_SUCCESS = 0
Public Const SQL_ERROR = —1
Public Const SQL_SUCCESS_WITH_INFO = 1
Public Const SQL_INVALID_HANDLE = —2
Public Const SQL_NO_DATA_FOUND = 100 
Sub OpenOdbcSource()

Dim nenv As Long

Dim nok As Integer

Dim cdatasource As String

Dim suserid As String

Dim spassword As String

nok = SQLAllocEnv(nenv)

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured During the Allocation of the ODBC Environment"

    Exit Sub

End If

nok = SQLAllocConnect(nenv, nconnect)

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured During the Allocation of ODBC Connections"

    Exit Sub

End If

' change the following 3 lines for your environment

cdatasource = "BIBLIO"

suserid = "ADMIN"

spassword = "PASSWORD"

nok = SQLConnect(nconnect, cdatasource, Len(cdatasource), suserid, Len(suserid), spassword, Len(spassword))

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured During the Conection to the ODBC Database"

    Exit Sub

End If

MsgBox " You have sucessfully connected to the ODBC Database"

End Sub

After you have opened the ODBC database, you can manipulate the data contained in the database. The following functions are necessary to select all of the records from a table (in this example, it is a table named Currencies):

  1. SQLAllocStmt. This API call will allocate a handle to the area in memory that will be used to store the result of SQL statements issued against the ODBC database. This memory will also be associated with the database connection that was obtained using the SQLAllocEnv API call.

  2. SQLExecDirect. This API call will execute the SQL statement that has been passed to it and return the result set to the handle that was obtained by the SQLAllocStmt API call.

Listing 29.12 illustrates the statements that are needed in addition to those in Listing 29.11 to create a recordset from the currency table stored in this ODBC database. 

Dim nsql As Long

Dim csql As String

nok = SQLAllocStmt(nconnect, nsql)

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured Allocating the SQL Results Handle"

    Exit Sub

End If

csql = " Select * from currency"

nok = SQLExecDirect(nsql, csql, Len(csql))

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured Excuting the SQL Statment"

    Exit Sub

End If

MsgBox "SQL Completed Sucessfully "

SQLNumResultsCol

After you have created an ODBC recordset, you can extract data from the table in order to place the information either into controls on a form or into variables that will be used later in your application. The following are the calls that are necessary to read the data from the fields contained in the recordset.

  1. SQLFetch. This API call retrieves a row of data from the recordset that was created by the SQL statement. In order to retrieve the row you need to specify the handle that was obtained by the SQLAllocStmt API call.

  2. SQLGetData. This API call retrieves the data contained in the columns of the recordset that was created by the SQL statement. In order to retrieve the data, you need to know which column of the recordset you want to retrieve, the type of data contained in that column, as well as the length of the data field.

Listing 29.13 illustrates the statements that are necessary in addition to those in Listings 29.11 and 29.12 to retrieve the value of a field from a recordset and to display it in a message box. 

Dim cdata As String

Dim nlong As Long

Const SQL_C_CHAR = 1

nok = SQLFetch(nsql)

If nok <> SQL_SUCCESS Then

    MsgBox "An Error Occured Obtaining the SQL Result Set"

    Exit Sub

End If

nok = SQLGetData(nsql, 1, SQL_C_CHAR, cdata, 30, nlong)

If nok <> SQL_SUCCESS Then

    processerror nenv, nconnect, 0

    MsgBox "An Error Occured Obtaining the Field Value"

    Exit Sub

Else

    MsgBox "The Following data was retreived " * cdata

End If

In the event of an error occurring during any of the ODBC API functions, the SQLError function will provide you with additional information in order to further diagnose the problem. In order to retrieve additional error information, you need to specify the following information for the SQLError function:

The function will then return the following information:

The example contained in the opening and closing of an ODBC Data Source section illustrates the use of the SQLError function.

When closing an application that is using the ODBC API, you must manually close all of the connections that were opened when initiating the application. The functions and steps involved in closing the connections consist of the following:

  1. SQLDisconnect. The SQLDisconnect function will remove the link between your application and the ODBC data source. You must supply this function with the database connection handle that was obtained by the SQLConnect API function.

  2. SQLFreeConnect. The SQLFreeConnect function will remove the handle to the database driver that was previously established using the SQLConnect API function. You must supply this function with the database connection handle that was obtained by the SQLConnect API function.

  3. SQLFreeEnv. The SQLFreeEnv function will remove the handle to the driver manager that was previously established using the SQLAlocEnv API function.

Opening and Closing an ODBC Data Source

The following example illustrates the opening and closing of an ODBC database using the ODBC API. Follow the steps listed here to create the example application(note that this is a 16-bit example):

  1. Using the File | New Project command, open a new project.

  2. Double-click on the Command Button control (CommandButton in ToolTips display) contained in the ToolBox in order to place a Command Button on your form.

  3. Include the following property settings for the Command Button control:

    Caption = &Open ODBC

    Name = cmdOpenOdbc

  4. Add the following to the general section of the form:

    Dim nenv As Long
    Dim nconnect As Long

  5. Enter the following code into the cmdOpenOdbc_Click event:

    Dim nok As Integer
    Dim cdatasource As String
    Dim suserid As String
    Dim spassword As String

    'change the following 3 lines for your environment

    cdatasource = "BIBLIO"
    suserid = "ADMIN"
    spassword = "PASSWORD"

    nok = sqlallocenv(nenv)
    If nok < SQL_SUCCESS Then

    processerror nenv, nconnect, 0
    Exit Sub

    End If

    nok = sqlalloconnect(nenv, nconnect)

    If nok < SQL_SUCCESS Then

    processerror nenv, nconnect, 0
    Exit Sub

    End If

    nok = SQLConnect(nconnect, cdatasource, Len(cdatasource), suserid, Len(suserid), spassword, Len(spassword))

    If nok < SQL_SUCCESS Then
    processerror nenv, nconnect, 0
    Exit Sub

    End If

  6. Double-click on the Command Button control (CommandButton in ToolTips display) contained in the ToolBox in order to place a Command Button on your form.

  7. Include the following property settings for the Command Button control:

    Caption = &Close ODBC
    Name = cmdCloseOdbc

    Enter the following code into the cmdCloseOdbc_Click event:

    Dim nok As Integer
    nok = SQLDisconnect(nconnect)

    If nok < SQL_SUCCESS Then

    processerror nenv, nconnect, 0
    Exit Sub

    End If

    nok = SQLFreeConnect(nconnect)

    If nok < SQL_SUCCESS Then

    processerror nenv, nconnect, 0
    Exit Sub

    End If

    nok = SQLFreeEnv(nenv)

    If nok < SQL_SUCCESS Then

    processerror nenv, nconnect, 0
    Exit Sub

    End If

  8. Create the following subroutine in the general declaration section of the form:

    Sub processerror(nenv As Long, nconnect As Long, nstatement As Long)

    Dim nok As Integer
    Dim csqlstate As String
    Dim snativeerror As Long
    Dim serrormessage As String * 255
    Dim nerrormsg As Integer

    nok = SQLError(nenv, nconnect, nstatement, csqlstate, snativeerror, serrormessage, Len(serrormessage), nerrormsg)

    MsgBox " the following Error" & serrormessage & " occurred Number " & snativerror

    End Sub

  9. Using the Insert | Module command, add a BAS module to your project.

  10. Enter the following declarations to the BAS module:

    Declare Function sqlallocenv Lib "odbc.dll" (ByVal phenv As Long) As Integer
    Declare Function sqlalloconnect Lib "odbc.dll" (ByVal henv As Long, phdbc As Long) As Integer

    Declare Function SQLConnect Lib "odbc.dll" (ByVal hdbc As Long, ByVal szDSN As String, ByVal cbDSN As Integer, ByVal szUID As String, ByVal cbUID As Integer, ByVal szAuthStr As String, ByVal cbAuthStr As Integer) As Integer

    Declare Function SQLError Lib "odbc.dll" (ByVal henv As Long, ByVal hdbc As Long, ByVal hstmt As Long, ByVal szSqlState As String, pfNativeError As Long, ByVal szErrorMsg As String, ByVal cbErrorMsgMax As Integer, pcbErrorMsg As Integer) As Integer

    Declare Function SQLDisconnect Lib "odbc.dll" (ByVal hdbc As Long) As Integer

    Declare Function SQLFreeConnect Lib "odbc.dll" (ByVal hdbc As Long) As _Integer
    Declare Function SQLFreeEnv Lib "odbc.dll" (ByVal henv As Long) As Integer

  11. Press the F5 key to run the project.

When the project is running, you can connect the ODBC data source as defined in the cmdOdbcConnect_Click event. If an error occurs during the connect, you will receive a message box containing the complete text of the error. Once you have opened a connection, you then can perform additional operations on the database using the examples presented earlier in this section. Press the Close button, and the connection to the database will be closed.

Summary

In this chapter, you looked at the features of ODBC as well as some of the benefits of ODBC. You learned to use a Data control to open an Oracle type database. You also explored the properties and methods of the Data control and the Data Access Objects that apply specifically to ODBC databases. Finally, you looked at a few of the ODBC API calls, their Visual Basic equivalents, as well as some examples illustrating the functionality of the ODBC API.

Previous Page TOC Next Page